﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using System.Collections;
using SCRUM.Core.DAO;
using System.Data.SqlClient;

namespace SCRUM.Core.DataProviders
{
    using SCRUM.Core.Exceptions.Database;

    /// <summary>
    /// Privider for comunication with the database for project
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Creates new Project.
        /// </summary>
        /// <param name="project">The specific project.</param>
        /// <returns>Created project with ID</returns>
        public override Project CreateProject(Project project) 
        {
            int projID = InsertProject(project);
            return GetProjectById(projID);
        }

        /// <summary>
        /// Inserts the project into database.
        /// </summary>
        /// <param name="project">The project.</param>
        /// <returns></returns>
        private int InsertProject(Project project)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", project.Name);
                DbParameter dbDescription = CreateParameter("description", project.Description);
                DbParameter dbState = CreateParameter("stateId", project.State.ID);
                DbParameter dbCust = CreateParameter("customer", project.Customer);
                DbParameter dbStart = CreateParameter("startDate", project.StartDate);
                DbParameter dbEst = CreateParameter("estimation", project.Estimation);

                connection.Open();

                int data = ExecuteNonQuery("project/CreateProject", dbState, dbName, dbStart, dbCust, dbDescription, dbEst);
                if (data != 1)
                {
                    throw new Exception("ProjectNotCreated");
                }

                int projectID = Convert.ToInt32(GetScalar("project/GetLastInsertedProjectID"));
                return projectID;
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("ProjectCouldNotBeCreated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the project.
        /// </summary>
        /// <param name="project">The specific project.</param>
        public override void UpdateProject(Project project)
        {
            try
            {
                DbParameter dbId = CreateParameter("id", project.ID);
                DbParameter dbName = CreateParameter("name", project.Name);
                DbParameter dbDescription = CreateParameter("description", project.Description);
                DbParameter dbState = CreateParameter("stateId", project.State.ID);
                DbParameter dbCust = CreateParameter("customer", project.Customer);
                DbParameter dbStart = CreateParameter("startDate", project.StartDate);
                DbParameter dbEst = CreateParameter("estimation", project.Estimation);

                connection.Open();
                int data = ExecuteNonQuery("project/UpdateProject", dbId, dbState, dbName, dbStart, dbCust, dbDescription , dbEst);
                if (data != 1)
                {
                    throw new Exception("ProjectNotFoundForUpdate");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("ProjectCouldNotBeUpdated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the project.
        /// </summary>
        /// <param name="id">The specific project.</param>
        public override void DeleteProject(int id)
        {
            try
            {
                DbParameter dbId = CreateParameter("projectId", id);

                connection.Open();

                int data = ExecuteNonQuery("project/DeleteProject", dbId);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("ProjectNotFoundForDelete");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("ProjectCannotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets the project from DbDataReader
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        private Project GetProject(DbDataReader data)
        {
            return new Project(
                GetIntByColumn(data, "ID")
                , GetIntByColumn(data, "STATE_ID")
                , GetStringByColumn(data, "NAME")
                , GetStringByColumn(data, "DESCRIPTION")
                , GetDoubleByColumn(data,"ESTIMATION")
                , GetDoubleByColumn(data, "PROGRESS")
                , GetDateByColumn(data, "START_DATE")
                , GetStringByColumn(data, "CUSTOMER")
                , GetDateByColumn(data, "CHANGED")
            );
        }

        /// <summary>
        /// Get project from projectID
        /// </summary>
        /// <param name="projectID"></param>
        /// 
        /// <returns></returns>
        public override IList<Project> GetAllProjects(int projectID, int releaseID, int teamID, string name, string customer, int stateID)
        {
            try
            {
                IList<Project> projects = new List<Project>();

                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbReleaseID = CreateParameter("releaseID", releaseID);
                DbParameter dbTeamID = CreateParameter("teamID", teamID);
                DbParameter dbName = CreateParameter("name", name);
                DbParameter dbCustomer = CreateParameter("customer", customer);
                DbParameter dbStateID = CreateParameter("stateID", stateID);

                connection.Open();

                DbDataReader data = GetGrid(
                    "project/GetAllProjects"
                    , dbProjectID
                    , dbReleaseID
                    , dbTeamID
                    , dbName
                    , dbCustomer
                    , dbStateID
                );

                while (data.Read())
                {
                    Project project = GetProject(data);
                    projects.Add(project);
                }
                return projects;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Get all projects.
        /// </summary>
        public override IList<Project> GetAllProjects()
        {
            return GetAllProjects(-1, -1, -1, null, null, -1);
        }

        /// <summary>
        /// Get project details.
        /// </summary>
        public override Project GetProjectById(int projectId)
        {
            IList<Project> projects = GetAllProjects(projectId, -1, -1, null, null, -1);
            if (projects.Count != 1)
            {
                throw new InconsistentDatabaseException("ProjectNotFound");
            }
            return projects[0];
        }

        /// <summary>
        /// Gets the project by release.
        /// </summary>
        /// <param name="releaseID">The release.</param>
        /// <returns>project the release belongs to</returns>
        public override Project GetProjectByReleaseID(int releaseID)
        {
            IList<Project> projects = GetAllProjects(-1, releaseID, -1, null, null, -1);
            if (projects.Count != 1)
            {
                throw new InconsistentDatabaseException("ProjectNotFound");
            }
            return projects[0];
        }

        /// <summary>
        /// Gets the project by team ID.
        /// </summary>
        /// <param name="teamID">The team ID.</param>
        /// <returns>Project the team is working at.</returns>
        public override Project GetProjectByTeam(int teamID)
        {
            //IList<Project> projects;
            DbParameter dbteamID = CreateParameter("teamID", teamID);

            // = GetAllProjects //GetAllProjects(-1, -1, teamID);

            try
            {
                connection.Open();
                DbDataReader data = GetGrid("project/GetProjectByTeam", dbteamID);

                if (data.Read())
                {
                    return GetProject(data);
                }
                else
                {
                    throw new InconsistentDatabaseException("ProjectNotFound");
                }
                //if (projects.Count != 1)
                //{
                //    throw new InconsistentDatabaseException("ProjectNotFound");
                //}
                //return projects[0];
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
